<?php
if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class m_laporan_laba extends CI_Model {

	public function __construct() {
		parent::__construct();
	}

	function get() {
		$sql = 'select o.kdobat, o.nmobat, k.nmkelompok, (0) as stok_awal,

					(select sum(db.jumlah*db.harga) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat) as beli,
					
					(select sum(dj.jumlah*dj.harga) from detail_jual dj, jual j
						where dj.kdjual = j.kdjual
						and dj.kdobat= o.kdobat) as jual,

					(select sum(db.jumlah) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat) * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
						and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
							INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
							where db.KdObat = o.KdObat)) - 
					(select sum(dj.jumlah) from detail_jual dj, jual j
						where dj.kdjual = j.kdjual and dj.kdobat= o.kdobat) * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
						and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
							INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
							where db.KdObat = o.KdObat)) as stok_akhir
				from obat o 
				inner join kelompok k on o.KdKelompok = k.KdKelompok 
				order by o.kdobat';
		return $sql;
	}

	function get_cari($tgl_awal,$tgl_akhir,$kelompok) {
		if($kelompok != ''){
			$where_kelompok = "where k.KdKelompok = '".$kelompok."'";
		}else{
			$where_kelompok = "";
		}
		$sql = 'select o.kdobat, o.nmobat, k.nmkelompok, 
					(select sum(db.jumlah) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat) * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
							and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
								INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
								where b.tanggal <= "'.$tgl_awal.'" AND db.KdObat = o.KdObat)) - 
							(select sum(dj.jumlah) from detail_jual dj, jual j
								where dj.kdjual = j.kdjual and dj.kdobat= o.kdobat and j.tanggal < "'.$tgl_awal.'") * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
							and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
								INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
								where b.tanggal <= "'.$tgl_awal.'" AND db.KdObat = o.KdObat)) as stok_awal,

					(select sum(db.jumlah*db.harga) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat 
						and b.tanggal between "'.$tgl_awal.'" and "'.$tgl_akhir.'") as beli,
					
					(select sum(dj.jumlah*dj.harga) from detail_jual dj, jual j
						where dj.kdjual = j.kdjual
						and dj.kdobat= o.kdobat 
						and j.tanggal between "'.$tgl_awal.'" and "'.$tgl_akhir.'") as jual,

					(select sum(db.jumlah) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat
						and b.tanggal < "'.$tgl_akhir.'") * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
						and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
							INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
							where b.tanggal <= "'.$tgl_akhir.'" AND db.KdObat = o.KdObat)) - 
					(select sum(dj.jumlah) from detail_jual dj, jual j
						where dj.kdjual = j.kdjual and dj.kdobat= o.kdobat and j.tanggal < "'.$tgl_akhir.'") * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
						and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
							INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
							where b.tanggal <= "'.$tgl_akhir.'" AND db.KdObat = o.KdObat)) as stok_akhir
				from obat o 
				inner join kelompok k on o.KdKelompok = k.KdKelompok 
				'.$where_kelompok.'
				order by o.kdobat';
		return $sql;
	}

	function get_cari_kelompok($kelompok) {
		if($kelompok != ''){
			$where_kelompok = "where k.KdKelompok = '".$kelompok."'";
		}else{
			$where_kelompok = "";
		}
		$sql = 'select o.kdobat, o.nmobat, k.nmkelompok, (0) as stok_awal,

					(select sum(db.jumlah*db.harga) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat) as beli,
					
					(select sum(dj.jumlah*dj.harga) from detail_jual dj, jual j
						where dj.kdjual = j.kdjual
						and dj.kdobat= o.kdobat) as jual,

					(select sum(db.jumlah) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat) * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
						and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
							INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
							where db.KdObat = o.KdObat)) - 
					(select sum(dj.jumlah) from detail_jual dj, jual j
						where dj.kdjual = j.kdjual and dj.kdobat= o.kdobat) * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
						and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
							INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
							where db.KdObat = o.KdObat)) as stok_akhir
				from obat o 
				inner join kelompok k on o.KdKelompok = k.KdKelompok 
				'.$where_kelompok.'
				order by o.kdobat';
		return $sql;
	}

	function get_kelompok() {
		$sql = 'SELECT * FROM kelompok';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_kelompok_by($KdKelompok) {
		$sql = 'SELECT * FROM kelompok WHERE KdKelompok = ?';
		$query = $this->db->query($sql,$KdKelompok);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_export_all() {
		$sql = 'select o.kdobat, o.nmobat, k.nmkelompok, (0) as stok_awal,

					(select sum(db.jumlah*db.harga) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat) as beli,
					
					(select sum(dj.jumlah*dj.harga) from detail_jual dj, jual j
						where dj.kdjual = j.kdjual
						and dj.kdobat= o.kdobat) as jual,

					(select sum(db.jumlah) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat) * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
						and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
							INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
							where db.KdObat = o.KdObat)) - 
					(select sum(dj.jumlah) from detail_jual dj, jual j
						where dj.kdjual = j.kdjual and dj.kdobat= o.kdobat) * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
						and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
							INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
							where db.KdObat = o.KdObat)) as stok_akhir
				from obat o 
				inner join kelompok k on o.KdKelompok = k.KdKelompok 
				order by o.kdobat';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_export_by_tanggal($tgl_awal,$tgl_akhir,$kelompok) {
		if($kelompok != ''){
			$where_kelompok = "where k.KdKelompok = '".$kelompok."'";
		}else{
			$where_kelompok = "";
		}
		$sql = 'select o.kdobat, o.nmobat, k.nmkelompok, 
					(select sum(db.jumlah) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat) * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
							and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
								INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
								where b.tanggal <= "'.$tgl_awal.'" AND db.KdObat = o.KdObat)) - 
							(select sum(dj.jumlah) from detail_jual dj, jual j
								where dj.kdjual = j.kdjual and dj.kdobat= o.kdobat and j.tanggal < "'.$tgl_awal.'") * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
							and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
								INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
								where b.tanggal <= "'.$tgl_awal.'" AND db.KdObat = o.KdObat)) as stok_awal,

					(select sum(db.jumlah*db.harga) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat 
						and b.tanggal between "'.$tgl_awal.'" and "'.$tgl_akhir.'") as beli,
					
					(select sum(dj.jumlah*dj.harga) from detail_jual dj, jual j
						where dj.kdjual = j.kdjual
						and dj.kdobat= o.kdobat 
						and j.tanggal between "'.$tgl_awal.'" and "'.$tgl_akhir.'") as jual,

					(select sum(db.jumlah) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat
						and b.tanggal < "'.$tgl_akhir.'") * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
						and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
							INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
							where b.tanggal <= "'.$tgl_akhir.'" AND db.KdObat = o.KdObat)) - 
					(select sum(dj.jumlah) from detail_jual dj, jual j
						where dj.kdjual = j.kdjual and dj.kdobat= o.kdobat and j.tanggal < "'.$tgl_akhir.'") * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
						and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
							INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
							where b.tanggal <= "'.$tgl_akhir.'" AND db.KdObat = o.KdObat)) as stok_akhir
				from obat o 
				inner join kelompok k on o.KdKelompok = k.KdKelompok 
				'.$where_kelompok.'
				order by o.kdobat';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_export_by_kelompok($kelompok) {
		if($kelompok != ''){
			$where_kelompok = "where k.KdKelompok = '".$kelompok."'";
		}else{
			$where_kelompok = "";
		}
		$sql = 'select o.kdobat, o.nmobat, k.nmkelompok, (0) as stok_awal,

					(select sum(db.jumlah*db.harga) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat) as beli,
					
					(select sum(dj.jumlah*dj.harga) from detail_jual dj, jual j
						where dj.kdjual = j.kdjual
						and dj.kdobat= o.kdobat) as jual,

					(select sum(db.jumlah) from detail_beli db, beli b
						where db.kdbeli = b.kdbeli
						and db.kdobat= o.kdobat) * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
						and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
							INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
							where db.KdObat = o.KdObat)) - 
					(select sum(dj.jumlah) from detail_jual dj, jual j
						where dj.kdjual = j.kdjual and dj.kdobat= o.kdobat) * 
					(select db.harga from detail_beli db, beli b
						where db.kdbeli = b.kdbeli and db.kdobat = o.kdobat
						and db.KdBeli = (SELECT max(b.KdBeli) FROM beli b 
							INNER JOIN detail_beli db ON b.KdBeli = db.KdBeli 
							where db.KdObat = o.KdObat)) as stok_akhir
				from obat o 
				inner join kelompok k on o.KdKelompok = k.KdKelompok 
				'.$where_kelompok.'
				order by o.kdobat';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}
}
